﻿using CloudDcPc.Entity;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CloudDcPc.Bean
{
    public class TabBean
    {
        private MySqlConnection myConn = null;
        private DBConnection dbc = null;
        private MySqlCommand cmd = null;
        public TabBean()
        {
            this.dbc = new DBConnection();
            this.myConn = this.dbc.getConnection();
        }

        public bool UpdateTabForError(int tn,string s)
        {
            string sql = "update dc_tab set status='" +s + "' where tab_num='" + tn+ "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed)System.Threading.Thread.Sleep(1000);myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                dbc.close();
            }

        }

        public List<Tab> GetTabsByField(string f, string w)
        {
            Tab tab = null;
            List<Tab> list = new List<Tab>();
            string sql = "select * from dc_tab where " + f + "='" + w + "' order by name";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    tab = new Tab()
                    {
                        Tab_num = (int)reader["tab_num"],
                        Name = reader["name"].ToString()
                    };
                    list.Add(tab);
                }
                return list;
            }
            finally
            {
                dbc.close();
            }
        }

        public bool HasTab(int tab_num)
        {
            string sql = "select id from dc_tab where tab_num='" + tab_num + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return true;
                }
                return false;
            }
            finally
            {
                dbc.close();
            }
        }
        public bool HasTabByName(string name)
        {
            string sql = "select id from dc_tab where name='" + name + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return true;
                }
                return false;
            }
            finally
            {
                dbc.close();
            }
        }


        public bool DelTabById(int id)
        {
            string sql = "delete from dc_tab where id='" + id + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed)System.Threading.Thread.Sleep(1000);myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                dbc.close();
            }
        }


        public bool AddTab(Tab tab)
        {
            string sql = "insert into dc_tab(tab_num,name,size,status,wait) values('" + tab.Tab_num + "','" + tab.Name + "','" + tab.Size + "','" + tab.Status + "','" + tab.Wait + "')";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed)System.Threading.Thread.Sleep(1000);myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                dbc.close();
            }
        }


        public bool UpdateTab(Tab tab)
        {
            string sql = "update dc_tab set tab_num='" + tab.Tab_num + "',name='" + tab.Name + "',size='" + tab.Size + "',status='" + tab.Status + "',wait='" + tab.Wait + "' where id='" + tab.Id + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed)System.Threading.Thread.Sleep(1000);myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                dbc.close();
            }

        }


        public ObservableCollection<Tab> GetAllTab()
        {
            ObservableCollection<Tab> list = new ObservableCollection<Tab>();
            Tab tab = null;
            string sql = "select * from dc_tab order by tab_num";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed)System.Threading.Thread.Sleep(1000);myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    tab = new Tab()
                    {
                        Id = (int)reader["id"],
                        Tab_num = (int)reader["tab_num"],
                        Name = reader["name"].ToString(),
                        Size = (int)reader["size"],
                        Status = (int)reader["status"],
                        Wait = (int)reader["wait"]
                    };
                    list.Add(tab);
                }

            }
            finally
            {
                dbc.close();
            }
            return list;
        }


        public List<Tab> GetTabs()
        {
            List<Tab> list = new List<Tab>();
            string sql = "select * from dc_tab  where wait='0' order by tab_num";
            Tab tab = null;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    tab = new Tab();
                    tab.Tab_num = (int)reader["tab_num"];
                    tab.Name = (string)reader["name"];
                    tab.Status = (int)reader["status"];
                    tab.Size = (int)reader["size"];
                    tab.Wait = (int)reader["wait"];
                    list.Add(tab);
                }
                return list;
            }
            finally
            {
                dbc.close();
            }
        }
        public Tab GetTabByField(string f,string w)
        {
            Tab tab = null; 
            string sql = "select * from dc_tab where " + f + "='" + w + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    tab = new Tab();
                    tab.Tab_num = (int)reader["tab_num"];
                    tab.Name = (string)reader["name"];
                    tab.Status = (int)reader["status"];
                    tab.Size = (int)reader["size"];
                    tab.Wait = (int)reader["wait"];
                }
                return tab;
            }
            finally
            {
                dbc.close();
            }
        }
    }
}
